OpenEdge Getting Started:<br />Database Essentials Keys

There are two types of keys: primary and foreign. A primary key is a column (or group of columns) whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows. A good primary key has these characteristics:

It is mandatory; that is, it must store non-null values. If the column is left blank, duplicate rows can occur.

It is unique. For example, the social security column in an Employee or Student table is a good key because it uniquely identifies each individual. The Cust Number column in the Customer table uniquely identifies each customer. It is less practical to use a person’s name because more than one customer might have the same name. Also, databases do not detect variations in names as duplicates (for example, Cathy for Catherine, Joe for Joseph). Furthermore, people do sometimes change their names (for example, through a marriage or divorce).

It is stable; that is, it is unlikely to change. As in the previous example, the social security number is a good key not only because it uniquely identifies each individual, but it is also unlikely to change, while a person’s or customer’s name might change.

It is short; that is, it has few characters. Smaller columns occupy less storage space, database searches are faster, and entries are less prone to mistakes. For example, a social security column of nine digits is easier to access than a name column of 30 characters.

A foreign key is a column value in one table that is required to match the column value of the primary key in another table. In other words, it is the reference by one table to another. If the foreign key value is not null, then the primary key value in the referenced table must exist. It is this relationship of a column in one table to a column in another table that provides the relational database with its ability to join tables. Chapter 2, "Database Design," describes this concept in more detail.